﻿Public Class ClsTAccountSumDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_ACCOUNT_SUM)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 返回指定年度的科目汇总的会计期间
    ''' </summary>
    ''' <param name="year"></param>
    ''' <returns></returns>
    Public Function GetAllPeriodByYear(ByVal year As String) As List(Of String)

        Dim tmpPeriod As List(Of String) = New List(Of String)()

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select distinct [WorkPeriod] from [" & Me.AppTableName & "] where [WorkPeriod] like @WorkPeriod & '%'  "


            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = year

            Dim tmpReader As OleDb.OleDbDataReader = tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)
            If tmpReader IsNot Nothing Then
                '循环取出
                While tmpReader.Read()
                    tmpPeriod.Add(ConvertDbNullSingle(tmpReader(0)))
                End While
            End If

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAccountSumInPeriod"

        End Try

        Return tmpPeriod

    End Function

    ''' <summary>
    ''' 删除指定会计期间的科目汇总
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function DeleteAcctSumByWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Delete * From [" & Me.AppTableName & "] "
            tmpSql &= " WHERE [WorkPeriod]=@WorkPeriod "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - DeleteAcctSumByWorkPeriod"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 更新科目汇总记录
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function UpdateAccountSum(ByVal tmpCls As ClsTAccountSum) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(7) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "UPDATE [" & Me.AppTableName & "] SET "
            tmpSql &= "[WorkPeriod]=@WorkPeriod,[AcctCode]=@AcctCode,[AcctLevel]=@AcctLevel,"
            tmpSql &= "[IfLastLevel]=@IfLastLevel,[AcctName]=@AcctName,"
            tmpSql &= "[CreditSum]=@CreditSum,[DebitSum]=@DebitSum"
            tmpSql &= " WHERE [AcctSumId]=@AcctSumId "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar）
            tmpParas(2) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer）
            tmpParas(3) = New OleDb.OleDbParameter("@IfLastLevel", OleDb.OleDbType.Integer）
            tmpParas(4) = New OleDb.OleDbParameter("@AcctName", OleDb.OleDbType.VarWChar)
            tmpParas(5) = New OleDb.OleDbParameter("@CreditSum", OleDb.OleDbType.Decimal)
            tmpParas(6) = New OleDb.OleDbParameter("@DebitSum", OleDb.OleDbType.Decimal)
            tmpParas(7) = New OleDb.OleDbParameter("@AcctSumId", OleDb.OleDbType.Integer)

            With tmpCls

                tmpParas(0).Value = .WorkPeriod
                tmpParas(1).Value = .AcctCode
                tmpParas(2).Value = .AcctLevel
                tmpParas(3).Value = .IfLastLevel
                tmpParas(4).Value = .AcctName
                tmpParas(5).Value = .CreditSum
                tmpParas(6).Value = .DebitSum
                tmpParas(7).Value = .AcctSumId

            End With


            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 插入一行新的科目汇总记录
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function InsertAccountSum(ByVal tmpCls As ClsTAccountSum) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(6) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "] ("
            tmpSql &= "WorkPeriod,AcctCode,AcctLevel,IfLastLevel,AcctName,"
            tmpSql &= "CreditSum,DebitSum"
            tmpSql &= ") Values ("
            tmpSql &= "@WorkPeriod,@AcctCode,@AcctLevel,@IfLastLevel,@AcctName,"
            tmpSql &= "@CreditSum,@DebitSum"
            tmpSql &= ")"

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar）
            tmpParas(2) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer）
            tmpParas(3) = New OleDb.OleDbParameter("@IfLastLevel", OleDb.OleDbType.Integer）
            tmpParas(4) = New OleDb.OleDbParameter("@AcctName", OleDb.OleDbType.VarWChar)
            tmpParas(5) = New OleDb.OleDbParameter("@CreditSum", OleDb.OleDbType.Decimal)
            tmpParas(6) = New OleDb.OleDbParameter("@DebitSum", OleDb.OleDbType.Decimal)

            With tmpCls

                tmpParas(0).Value = .WorkPeriod
                tmpParas(1).Value = .AcctCode
                tmpParas(2).Value = .AcctLevel
                tmpParas(3).Value = .IfLastLevel
                tmpParas(4).Value = .AcctName
                tmpParas(5).Value = .CreditSum
                tmpParas(6).Value = .DebitSum

            End With


            tmpRetInt = tmpDbAccess.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - InsertAccountSum"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 更新科目年初数
    ''' </summary>
    ''' <param name="acct"></param>
    ''' <param name="yearMoney"></param>
    ''' <returns></returns>
    Public Function UpdateYear(ByVal acct As String, ByVal yearMoney As Decimal) As Integer

        Dim tmpRetInt As Integer

        Try

            Dim tmpSql As String
            Dim tmpParas(1) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set "
            tmpSql &= " [Year]=@Year "
            tmpSql &= "where  [AcctCode]=@AcctCode "

            tmpParas(0) = New OleDb.OleDbParameter("@Year", OleDb.OleDbType.Decimal)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = yearMoney
            tmpParas(1).Value = acct

            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - UpdateYear"

        End Try

        Return tmpRetInt

    End Function

    Public Function GetAccountSumInPeriod(ByVal wp As String, ByVal acct As String) As ClsTAccountSum

        Dim tmpCls As ClsTAccountSum = Nothing

        Try

            Dim tmpSql As String
            Dim tmpParas(1) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsTAccountSum) = New List(Of ClsTAccountSum)()
            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpSql &= " and [AcctCode]=@AcctCode "


            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctCode", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = wp
            tmpParas(1).Value = acct

            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTAccountSum))

            If tmpList IsNot Nothing AndAlso tmpList.Count > 0 Then
                tmpCls = tmpList.Item(0)
            End If

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAccountSumInPeriod"

        End Try

        Return tmpCls

    End Function

    ''' <summary>
    ''' 返回指定期间的全部科目汇总数据
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <param name="level">指定科目的级次，0代表全部</param>
    ''' <returns></returns>
    Public Function GetAllAccountSumInPeriod(ByVal wp As String, Optional ByVal level As Integer = 0) As List(Of ClsTAccountSum)

        Dim tmpList As List(Of ClsTAccountSum) = New List(Of ClsTAccountSum)()

        Try

            Dim tmpSql As String
            Dim tmpParas(1) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            If level > 0 Then
                tmpSql &= " and [AcctLevel]=@AcctLevel "
            End If

            tmpSql &= " order by [AcctCode] "

            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AcctLevel", OleDb.OleDbType.Integer)

            tmpParas(0).Value = wp
            tmpParas(1).Value = level

            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTAccountSum))

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - GetAllAccountSumInPeriod"

        End Try

        Return tmpList

    End Function

    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
        If reader Is Nothing Then
            Return Nothing
        End If

        Dim tmpList As List(Of ClsTAccountSum) = New List(Of ClsTAccountSum)

        Try

            Dim tmpCls As ClsTAccountSum

            While reader.Read()

                tmpCls = New ClsTAccountSum()

                With tmpCls

                    .AcctSumId = reader.GetInt32(0)
                    .WorkPeriod = reader.GetString(1)
                    .AcctCode = reader.GetString(2)
                    .AcctLevel = reader.GetInt16(3)
                    .IfLastLevel = reader.GetBoolean(4)
                    .AcctName = reader.GetString(5)
                    .CreditSum = reader.GetDecimal(6)
                    .DebitSum = reader.GetDecimal(7)

                End With

                tmpList.Add(tmpCls)

            End While

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTAccountSumDbAccess - ConvertRecordToClass"

        End Try

        Return tmpList

    End Function
End Class
